package com.boho.system.dao;


             
/**
 * 
 *
 * @File name:  TSysUserDao.java 
 * @Create on:  2010-05-10 11:27:218
 * @Author   :  陈伟
 *
 * @ChangeList
 * ---------------------------------------------------
 * Date         Editor              ChangeReasons
 *
 */
           

            
import java.sql.Connection;
import java.util.List;

import cn.boho.framework.po.CQuery;
import cn.boho.framework.po.CQueryFactoryTool;
import cn.boho.framework.po.ComboPager;
import cn.boho.framework.po.DAO;
import cn.boho.framework.po.DynaBeanMap;
import cn.boho.framework.po.Pager;

import com.boho.common.Constant;
            
        /**
 * 【系统后台用户:T_SYS_USER】的Dao操作类
 * 
 */
public class TSysUserDao extends DAO {
	
	/**
	* 分页查询所有【系统后台用户:T_USER】信息
	* @param con
	* @param pager
	* @param loginName
	* @param username
	* @param isLock
	* @return
	* @throws Exception
	* @author 陈伟
	*
	*/
	public static ComboPager queryTSysUserByPager(Connection con,Pager pager, String loginName, String username, Integer isLock)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select USER_ID, LOGIN_NAME, USER_NAME, IS_LOCK from T_USER where 1 = 1");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		int i = 1;
		if(loginName != null && loginName.toString().length() > 0){
			sql.append(" and lower(LOGIN_NAME) like ?");
			query.setString(++i, "%"+loginName.toLowerCase()+"%");
		}
		if(username != null && username.length() > 0){
			sql.append(" and lower(USER_NAME) like ?");
			query.setString(++i, "%"+username.toLowerCase()+"%");
		}
		if(isLock != null && isLock.toString().length() > 0){
			sql.append(" and IS_LOCK = ?");
			query.setInt(++i, isLock);
		}
		sql.append(" and USER_ID <>");
		sql.append(Constant.ADMIN_NO);
		sql.append(" order by USER_ID asc");
		query.setCommand(sql.toString());
		logger.debug("TSysUserDao.queryTSysUserByPager() SQL->"+query.getCommand());
		return query.getDynaBeanMapComboPager("T_USER", pager,con);
	}

	/**
	* 根据主键【用户ID:USER_ID|】获得信息
	* @param con
	* @param USER_ID 用户ID
	* @return
	* @throws Exception
	* @author 陈伟
	*
	*/
	@SuppressWarnings("unchecked")
	public static DynaBeanMap getTSysUserByPk(Connection con,Integer userID)throws Exception{
		DynaBeanMap dynaBeanMap = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select USER_ID, LOGIN_NAME, USER_NAME, PHONE, EMAIL, REMARK, ");
		sql.append("LAST_LOGIN_TIME, LAST_LOGIN_IP, IS_LOCK from T_USER where USER_ID=?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setInt(1,userID);
		logger.debug("TSysUserDao.getTSysUserByPk() SQL->"+query.getCommand());
		dynaBeanMap = query.getDynaBeanMap("T_USER", con);
		return dynaBeanMap;
	}
	
	/**
	 * 根据用户ID查询用户密码
	 * @param con
	 * @param userID
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static DynaBeanMap getUserPWDByPK(Connection con, Integer userID) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select PASSWORD from T_USER where USER_ID = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setInt(1, userID);
		logger.debug("TSysUserDao.getUserPWDByPK() SQL->"+query.getCommand());
		return query.getDynaBeanMap("T_USER", con);
	}
	
	/**
	 * 根据登录用户名查询用户信息
	 * @param con
	 * @param mobile
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static DynaBeanMap getUserByLoginName(Connection con, String loginName) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select USER_ID, LOGIN_NAME, PASSWORD, USER_NAME, PHONE, EMAIL, REMARK,  ");
		sql.append("LAST_LOGIN_TIME, LAST_LOGIN_IP, IS_LOCK from T_USER where LOGIN_NAME = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setString(1,loginName);
		logger.debug("TSysUserDao.getUserByLoginName() SQL->"+query.getCommand());
		return query.getDynaBeanMap("T_USER", con);
	}
	
	/**
	* 根据主键【用户ID:USER_ID】获得信息
	* @param con
	* @param USER_ID 用户ID
	* @return
	* @throws Exception
	* @author 陈伟
	*
	*/
	@SuppressWarnings("unchecked")
	public static DynaBeanMap getTSysUserRoleByPk(Connection con, Integer userID)throws Exception{
		DynaBeanMap dynaBeanMap = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select ROLE_ID from T_USER_ROLE where USER_ID=?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setInt(1,userID);
		dynaBeanMap = query.getDynaBeanMap("T_USER_ROLE", con);
		logger.debug("TSysUserDao.getTSysUserRoleByPk() SQL->"+query.getCommand());
		return dynaBeanMap;
	}
	
	/**
	 * 根据用户ID查询用户拥有的角色
	 * @param con
	 * @param userid
	 * @return
	 * @throws Exception
	 * @author 陈伟
	 */
	@SuppressWarnings("unchecked")
	public static List queryUserRoleByPKUser(Connection con, Integer userid) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select ROLE_ID from T_USER_ROLE where USER_ID = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setInt(1, userid);
		query.setCommand(sql.toString());
		logger.debug("TSysUserDao.queryUserRoleByPKUser() SQL->"+query.getCommand());
		return query.getDynaBeanMapList("T_USER_ROLE", con);
	}
	
	/**
	 * 根据用户ID查询用户拥有的角色
	 * @param con
	 * @param userid
	 * @return
	 * @throws Exception
	 * @author 陈伟
	 */
	@SuppressWarnings("unchecked")
	public static List queryUserRoleList(Connection con, Integer userid) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select ts.ROLE_NAME ROLE_NAME from T_USER_ROLE tss, T_ROLE ts ");
		sql.append("where tss.ROLE_ID = ts.ROLE_ID and tss.USER_ID = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setInt(1, userid);
		query.setCommand(sql.toString());
		logger.debug("TSysUserDao.queryUserRoleByPKUser() SQL->"+query.getCommand());
		return query.getDynaBeanMapList("T_USER_ROLE", con);
	}
	
	/**
	 * 根据用户ID查询用户拥有的功能
	 * @param con
	 * @param userid
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static List queryUserFuncByPKUser(Connection con, Integer userid) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select FUNCTION_ID from T_USER_FUNCTION where USER_ID = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setInt(1, userid);
		query.setCommand(sql.toString());
		logger.debug("TSysUserDao.queryUserFuncByPKUser() SQL->"+query.getCommand());
		return query.getDynaBeanMapList("T_USER_FUNCTION", con);
	}
		
	/**
	 * 查询用户附加的功能
	 * @param con
	 * @param userid
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static List queryUserFuncByPKUserAppend(Connection con, Integer userid) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select f.FUNCTION_ID, f.FUNCTION_NAME, f.FUNCTION_DESC, ");
		sql.append("f.PARENT_FUNCTION, f.FUNCTION_LEVEL, f.URL ");
		sql.append("from T_USER_FUNCTION uf, T_FUNCTION f ");
		sql.append("where uf.FUNCTION_ID = f.FUNCTION_ID ");
		sql.append("and uf.USER_ID = ? ");
		sql.append("order by f.FUNCTION_ID asc");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setInt(1, userid);
		query.setCommand(sql.toString());
		logger.debug("TSysUserDao.queryUserFuncByPKUserAppend() SQL->"+query.getCommand());
		return query.getDynaBeanMapList("T_USER_FUNCTION", con);
	}
	
	public static List queryUserDepartmentById(Connection con, Integer userid) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select DEPARTMENT_NO from T_USER_DEPARTMENT where USER_ID = ? order by DEPARTMENT_NO asc");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setInt(1, userid);
		query.setCommand(sql.toString());
		return query.getDynaBeanMapList("T_USER_DEPARTMENT", con);
	}
}
